SET XACT_ABORT OFF
SET ARITHABORT OFF

/* DECLARE GLOBAL VARIABLES */
DECLARE @rptPK int
DECLARE @rptGroupPK int
DECLARE @tempPK int
DECLARE @errorcount int
DECLARE @dsql varchar(8000)
DECLARE @version decimal(9,2)

SELECT @version = CAST(REPLACE(LOWER(schema_version), 'sp', '') AS decimal(9,2)) FROM _schema

/* =====================================================
UPDATE REPORTS STYLES AND GROUP HEADERS - (RPTLISTSmartAudit)
===================================================== */

IF @version >= 3.0 
BEGIN 
EXEC('PRINT ''Version 3.0 or greater detected. Importing Report Styles and Group Headers''
DECLARE @tempPK int
IF NOT EXISTS(SELECT ReportStyleName FROM ReportStyle WHERE ReportStyleName = ''Default'')
BEGIN 
INSERT INTO ReportStyle (ReportStyleName, ReportStyleDesc, ReportStyleCSS, IsDefault, IsBase, RowVersionIPAddress, RowVersionUserPK, RowVersionInitials, RowVersionAction, RowVersionDate) VALUES(''Default'', null, '' .pageselect{FONT-SIZE: 9pt; COLOR: #333333; FONT-FAMILY: Arial}
 .heading {background-color:#ffffff; cursor:pointer; FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: royalblue; FONT-FAMILY: Arial; z-index: 2500;} 
 .legendHeader {FONT-WEIGHT: bold; FONT-SIZE: 14px; COLOR: #333333; FONT-FAMILY: Arial}
 .normaltext {FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial}
 .labels {FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: royalblue; FONT-FAMILY: Arial}
 .assetUP {FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: green; FONT-FAMILY: Arial}
 .assetDOWN {FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: #DD0000; FONT-FAMILY: Arial}
 .asset {FONT-WEIGHT: normal; FONT-SIZE: 12px; COLOR: #000000; FONT-FAMILY: Arial}
 .data {FONT-SIZE: 12px; COLOR: #494949; FONT-FAMILY: Arial}
 .data_underline {BORDER-RIGHT: medium none; BORDER-TOP: medium none; FONT-SIZE: 12px; BORDER-LEFT: medium none; COLOR: #494949; BORDER-BOTTOM: #333333 1px solid; FONT-FAMILY: Arial}
 .bottomline {BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: #333333 1px solid}
 .buttons {FONT-SIZE: 12px; WIDTH: 80px; cursor: pointer; COLOR: #333333; FONT-FAMILY: Arial}
 .subtotal {BORDER-RIGHT: medium none; BORDER-TOP: #C0C0C0 1px solid; FONT-SIZE: 12px; BORDER-LEFT: medium none; COLOR: #333333; BORDER-BOTTOM: medium none; FONT-FAMILY: Arial}
 .bodyclasspreview {background-color:#ffffff; padding:10px; scrollbar-base-color: #FBFBFB; font-size:8pt; font-family:Arial; color:#000000;}
 .bodyclasspreviewinwo {background-color:#ffffff; padding-right:10px; scrollbar-base-color: #FBFBFB; font-size:8pt; font-family:Arial; color:#000000;}
 .bodyclassprint {background-color:#ffffff; PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px; font-size:8pt; font-family:Arial; color:#000000;}
 .bodyclassemail {background-color:#ffffff; PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-BOTTOM: 20px; PADDING-TOP: 0px; font-size:8pt; font-family:Arial; color:#000000;}
 .group1 {padding-left:0px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #acc5e7;}
 .group2 {padding-left:10px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #c7d7ed;}
 .group3 {padding-left:20px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #dce8f4;}
 .group4 {padding-left:30px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #ecf1fb;}
 .group5 {padding-left:40px; FONT-SIZE: 12px; COLOR: royalblue; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #ffffff;}
 .groupheader {FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold;}
 .normalright {BORDER-RIGHT: #c0c0c0 1px solid; BORDER-TOP: #c0c0c0 1px solid; PADDING-LEFT: 1px; FONT-WEIGHT: normal; FONT-SIZE: 8pt; MARGIN-BOTTOM: 1px; BORDER-LEFT: #c0c0c0 1px solid; COLOR: #000000; BORDER-BOTTOM: #c0c0c0 1px solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right}
 .clsBtnUp {cursor: pointer; color: black; font-weight: normal; border-top:1px solid #ffffff;border-left:1px solid #ffffff;border-right:1px solid #B4B4B4;border-bottom:1px solid #B4B4B4;padding-right:2px;}
 .clsBtnDown {cursor: pointer; color: black; font-weight: normal; border-right:1px solid #ffffff;border-bottom:1px solid #ffffff;border-top:1px solid #B4B4B4;border-left:1px solid #B4B4B4;padding-right:2px;}
 .clsBtnOff {color: black; font-weight: normal; tab-index: 0; border:1px solid transparent; padding-right:2px;}
 .actionbarlabel {float:right;margin-top:6px;padding-left:5px;padding-right:5px;font-size:8pt;font-family:Arial;color:#000000;}
 INPUT {padding-left:3px;}
 A:link {FONT-SIZE: 8pt; cursor: pointer; COLOR: #315aad; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent;}
 A:visited {FONT-SIZE: 8pt; cursor: pointer; COLOR: #315aad; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent;}
 A:active {FONT-SIZE: 8pt; cursor: pointer; COLOR: #315aad; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent;}
 A:hover {COLOR: red;}
 fieldset {border: 1px solid #AAAAAB;}
 .buttonsdisabled { display: static; opacity: 0.4; cursor:pointer;	}
 .buttonsenabled { display:	; opacity: 1; cursor:pointer; }
 .normalrow {FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial }
 .tb {width:100%; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;}
 .tbf {BACKGROUND-COLOR: #ffffcc; width:100%; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;}
 .ta {width:200px; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;}
 .taf {BACKGROUND-COLOR: #ffffcc; width:200px; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;}
 .cb {COLOR: #333333;}
 .HeaderRight {font-family:Arial;font-size:16px;color:#333333;font-weight:bold}
 .SubHeaderRight {font-family:Arial;font-size:11px;font-weight:normal}
 .SRInstructions {margin-top:5px;font-family:Arial;font-size:8pt;color:green;font-weight:bold}
 .verticalcolumn {border:1px solid #CCCCCC;}
 .mcpagebreak {page-break-before: always;}
 .ReportRow1 {background-color:#FFFFFF; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial }
 .ReportRow2 {background-color:#EFEFEF; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial }
 .ReportRowCrit1 {background-color:#FFFFFF; FONT-SIZE: 8pt; COLOR: #333333; FONT-FAMILY: Arial }
 .ReportRowCrit2 {background-color:#EFEFEF; FONT-SIZE: 8pt; COLOR: #333333; FONT-FAMILY: Arial }
 .SmartRow {background-color:#FFDF84; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; cursor:pointer; }
 .SubReportRow {background-color:#DEEFC6; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial }
 .ExpandCollapse {font-family:Arial;font-size:8pt;color:#333333;cursor:pointer;}'', 0, 1, '''', '''', ''_MC'', '''', getdate())
END 
')
END 
/* ============================================================
ReportID: RPTLISTSmartAudit
Report Name: Report Smart Button Value w/ fields
============================================================ */

IF EXISTS (
SELECT ReportName FROM Reports WITH (NOLOCK)
WHERE ReportID = 'RPTLISTSmartAudit')

BEGIN

PRINT '*******************************************************'
PRINT 'Report Exists - Updating...'
PRINT 'Report: Report Smart Button Value w/ fields'
PRINT '*******************************************************'

/* ================================================
UPDATE REPORT RECORDS - (RPTLISTSmartAudit)
================================================ */
/* Set ReportPK for this Report */
SELECT @rptPK = ReportPK FROM Reports WITH (NOLOCK) WHERE ReportID='RPTLISTSmartAudit'

/* Update Main Report Fields */
UPDATE Reports
SET	[ReportIDPriorToCopy]='RPTLISTSmartAudit', [ReportDesc]='This report will display the <b>Smart Button Field Value</b>. As a general rule, this field should be the <b>PK of the table being edited</b>.
For example, any change to a WO should display WO.WOPK in the Smart Button Field Value column.
<p>For more information clik here: <a href="https://uc.maintenanceconnection.com/hc/en-us/articles/203389184-Reporter-Tutorial-Advanced#editable" target="_blank" >Editable smart reports</a></p>
', [Sort1]='ReportGroup.ReportGroupName', [Sort2]='Reports.ReportName', [Sort3]='Reports.ReportID', [Sort4]=null, [Sort5]=null, [Sort1DESC]=0, [Sort2DESC]=0, [Sort3DESC]=0, [Sort4DESC]=0, [Sort5DESC]=0, [Group1]=1, [Group2]=0, [Group3]=0, [Group4]=0, [Group5]=0, [Header1]=0, [Header2]=0, [Header3]=0, [Header4]=0, [Header5]=0, [GroupHeader1]=null, [GroupHeader2]=null, [GroupHeader3]=null, [GroupHeader4]=null, [GroupHeader5]=null, [Total1]=0, [Total2]=0, [Total3]=0, [Total4]=0, [Total5]=0, [Chart]=null, [ChartName]=null, [ChartField]='SORT1', [ChartSize]=null, [ReportFile]='rpt_generic1.asp', [FromSQL]='FROM Reports', [JoinSQL]='LEFT JOIN ReportFields with(nolock) ON ReportFields.ReportPK = Reports.ReportPK AND ReportFields.SLAction = ''EF''
LEFT JOIN Report_ReportGroup ON Report_ReportGroup.REportPK = Reports.ReportPK
LEFT JOIN ReportGroup ON ReportGroup.ReportGroupPK = Report_ReportGroup.ReportGroupPK', [WhereSQL]='(Reports.USEDFOR = ''REPORTS'' or Reports.USEDFOR Is NULL)
 AND Reports.SDDisplay != ''''
 AND Reports.SDDisplay IS NOT NULL
 AND (Reports.SDPKField != (SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
  AND TABLE_NAME = ReportFields.RFTable)
 OR ReportFields.RFTable IN (SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
  GROUP BY TABLE_NAME
  HAVING COUNT(COLUMN_NAME) > 1))', [GroupBy]=0, [hits]=37, [Sequence]=0, [Layout]='hor', [VertCols]=1, [PageBreakEachRecord]=0, [Custom]=0, [ReportCopy]=1, [MCRegistrationDB]=0, [PrintCriteria]=1, [Active]=1, [UDFChar1]=null, [UDFChar2]=null, [UDFChar3]=null, [UDFChar4]='N', [UDFChar5]=null, [UDFDate1]=null, [UDFDate2]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionAction]='EDIT', [RowVersionDate]=getdate() , [ChartFunction]=null, [ChartFunctionField]=null, [NoDetail]=0, [PB1]=0, [PB2]=0, [PB3]=0, [PB4]=0, [PB5]=0, [SLDefault]=0, [SLType]=' ', [SLAction]='PW', [SLModuleID]='  ', [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLTooltip]=null, [SDDisplay]='     ', [SDModuleID]='  ', [SDPKField]=null, [SmartEmail]=0, [ChartPosition]=null, [ChartFormat]='I', [ChartSQL]=null, [Chart2]=null, [ChartName2]=null, [ChartField2]=null, [ChartSize2]=null, [ChartFormat2]=null, [ChartFunction2]=null, [ChartFunctionField2]=null, [ChartPosition2]=null, [ChartSQL2]=null, [Chart3]=null, [ChartName3]=null, [ChartField3]=null, [ChartSize3]=null, [ChartFormat3]=null, [ChartFunction3]=null, [ChartFunctionField3]=null, [ChartPosition3]=null, [ChartSQL3]=null, [ChartOnly]=0, [NoHeader]=0, [SRID1]=null, [SRPKField1]=null, [SRID2]=null, [SRPKField2]=null, [SRID3]=null, [SRPKField3]=null, [SRID4]=null, [SRPKField4]=null, [SRID5]=null, [SRPKField5]=null, [ReportPageSize]='Default', [ReportWidth]='100%', [PhotoCriteria]=0, [ReportStyleName]='Default', [UsedFor]='REPORTS', [SmartEmailLaborPK]=0, [SCDefault]='H', [SCField1]=null, [SCField2]=null, [SCField3]=null, [ReportStyleFontSize]=null, [ReportStyleFontColor]=null, [ReportStyleFontFamily]=null 
WHERE ReportPK = @rptPK
IF (@@error > 0) SET @errorcount = @errorcount + 1

IF @version >= 3.0 
BEGIN
SET @dSQL = 'UPDATE Reports SET [HavingSQL]=null, [DisplayPivotBar]=0, [DisplayColumnLines]=0, [DisplayTitleonPageBreak]=0, [DisplayFormatCriteria]=0, [R1T]='' '', [R1O]=''SQL            '', [R1V1]=null, [R1V2]=null, [R1A]=2, [R1L]=0, [R1F]=0, [R1CS]=''border: #0066CC 2px solid;'', [R1AF]=''C'', [R2T]='' '', [R2O]=null, [R2V1]=null, [R2V2]=null, [R2A]=0, [R2L]=0, [R2F]=0, [R2CS]=''border: #0066CC 2px solid;'', [R2AF]=''C'', [R3T]='' '', [R3O]=null, [R3V1]=null, [R3V2]=null, [R3A]=0, [R3L]=0, [R3F]=0, [R3CS]=''border: #0066CC 2px solid;'', [R3AF]=''C'' WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1

END

IF @version >= 4.2 
BEGIN
SET @dSQL = 'UPDATE Reports SET [DisplayDescription]=0 WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1

END

PRINT 'Updating Report - RPTLISTSmartAudit'

/* ==================================================
DELETE AND INSERT REPORT GROUPS - (RPTLISTSmartAudit)
=================================================== */
DELETE FROM Report_ReportGroup WHERE ReportPK = @rptPK

PRINT 'Deleting Report_ReportGroup Rows - RPTLISTSmartAudit'

IF (@@error > 0) SET @errorcount = @errorcount + 1

/* Make sure the report group actually exists */
IF NOT EXISTS (SELECT ReportGroupPK FROM ReportGroup WHERE ReportGroupPK = 54)
BEGIN
INSERT INTO ReportGroup ([ReportGroupID], [ReportGroupName], [ModuleID], [Sequence], [Icon], [RepairCenterPK], [IsUserGroup], [IsBatchGroup], [UDFChar1], [UDFChar2], [UDFChar3], [UDFChar4], [UDFChar5], [UDFDate1], [UDFDate2], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionAction], [RowVersionDate]) 
VALUES ('SY', 'System', 'SY', 99, null, null, 0, 0, null, null, null, null, null, null, null, null, '', '', '_MC', 'CREATE', getdate())
PRINT 'Inserting ReportGroup Row - ' + 'System'

IF (@@error > 0) SET @errorcount = @errorcount + 1

SET @rptGroupPK = @@IDENTITY
END
ELSE
BEGIN
UPDATE	ReportGroup
SET	[ReportGroupID]='SY', [ReportGroupName]='System', [ModuleID]='SY', [Sequence]=99, [Icon]=null, [RepairCenterPK]=null, [IsUserGroup]=0, [IsBatchGroup]=0, [UDFChar1]=null, [UDFChar2]=null, [UDFChar3]=null, [UDFChar4]=null, [UDFChar5]=null, [UDFDate1]=null, [UDFDate2]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionAction]='EDIT', [RowVersionDate]=getdate()
WHERE	ReportGroupPK = 54
PRINT 'Updating ReportGroup Row - ' + 'System'

SET @rptGroupPK = 54
END

IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO Report_ReportGroup ([ReportPK], [ReportGroupPK], [DemoLaborPK], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, @rptGroupPK, '', '', '_MC', getdate())
PRINT 'Inserting Report_ReportGroup Row - ' + 'System'
IF (@@error > 0) SET @errorcount = @errorcount + 1


/* ==================================================
DELETE AND INSERT REPORT TABLES - (RPTLISTSmartAudit)
=================================================== */
DELETE FROM ReportTables WHERE ReportPK = @rptPK

PRINT 'Deleting ReportTables Rows - RPTLISTSmartAudit'
IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportFields', null, 0, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportTables Row - ReportFields'
IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportGroup', null, 0, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportTables Row - ReportGroup'
IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', null, 0, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportTables Row - Reports'
IF (@@error > 0) SET @errorcount = @errorcount + 1


/* ==================================================
UPDATE OR INSERT REPORT CRITERIA - (RPTLISTSmartAudit)
=================================================== */	
IF EXISTS (
SELECT ReportCriteriaPK FROM ReportCriteria WITH (NOLOCK) WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportID')
BEGIN
UPDATE ReportCriteria
SET	[SQLWhereTable]='Reports', [SQLWhereField]='ReportID', [CritName]=null, [Operator]=null, [isMulti]=0, [AskLater]=1, [LabelOverride]=null, [DisplayOrder]=0, [FK_LookupOverride]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate()
WHERE	ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportID' 
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportCriteria Row - Reports.ReportID'
END
ELSE
BEGIN
INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportID', 'Reports', 'ReportID', null, null, null, 0, 1, null, 0, null, null, null, null, '_MC', getdate())
PRINT 'Inserting ReportCriteria Row - Reports.ReportID'
IF (@@error > 0) SET @errorcount = @errorcount + 1

END

IF EXISTS (
SELECT ReportCriteriaPK FROM ReportCriteria WITH (NOLOCK) WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportName')
BEGIN
UPDATE ReportCriteria
SET	[SQLWhereTable]='Reports', [SQLWhereField]='ReportName', [CritName]=null, [Operator]=null, [isMulti]=0, [AskLater]=1, [LabelOverride]=null, [DisplayOrder]=1, [FK_LookupOverride]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate()
WHERE	ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportName' 
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportCriteria Row - Reports.ReportName'
END
ELSE
BEGIN
INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportName', 'Reports', 'ReportName', null, null, null, 0, 1, null, 1, null, null, null, null, '_MC', getdate())
PRINT 'Inserting ReportCriteria Row - Reports.ReportName'
IF (@@error > 0) SET @errorcount = @errorcount + 1

END

IF EXISTS (
SELECT ReportCriteriaPK FROM ReportCriteria WITH (NOLOCK) WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'Active')
BEGIN
UPDATE ReportCriteria
SET	[SQLWhereTable]='Reports', [SQLWhereField]='Active', [CritName]=null, [Operator]=null, [isMulti]=0, [AskLater]=1, [LabelOverride]=null, [DisplayOrder]=2, [FK_LookupOverride]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate()
WHERE	ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'Active' 
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportCriteria Row - Reports.Active'
END
ELSE
BEGIN
INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'Active', 'Reports', 'Active', null, null, null, 0, 1, null, 2, null, null, null, null, '_MC', getdate())
PRINT 'Inserting ReportCriteria Row - Reports.Active'
IF (@@error > 0) SET @errorcount = @errorcount + 1

END

/* ==================================================
UPDATE OR INSERT REPORT FIELDS - (RPTLISTSmartAudit)
=================================================== */	
IF EXISTS (
SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'ReportName')
BEGIN
SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'ReportName'
UPDATE ReportFields
SET [ReportPK]=@rptPK, [DataDictPK]=3716, [AGFunction]=null, [Alias]=null, [DisplayOrder]=0, [Display]=1, [NotUserSelectable]=0, [LabelOverride]=null, [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=0, [CustomExpression]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]=null, [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null 
WHERE [ReportPK]=@rptPK AND [RFTable]='Reports' AND [RFField]='ReportName'
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportFields Row - Reports.ReportName'
END
ELSE
BEGIN
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3716, null, 'Reports', 'ReportName', null, 0, 1, 0, null, 0, 0, 0, null, null, '', '', '_MC', getdate(), null, null, null, null, null, null)
PRINT 'Inserting ReportFields Row - Reports.ReportName'
IF (@@error > 0) 
BEGIN
SET @errorcount = @errorcount + 1
SET @tempPK = -1
END
ELSE
BEGIN
SET @tempPK = @@IDENTITY
END
END

IF @version >= 4.2 
BEGIN
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = null, [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
IF EXISTS (
SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'SCField1')
BEGIN
SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'SCField1'
UPDATE ReportFields
SET [ReportPK]=@rptPK, [DataDictPK]=7994, [AGFunction]=null, [Alias]=null, [DisplayOrder]=1, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Smart Button Field Value', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=1, [CustomExpression]='Reports.SDPKField', [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]='  ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null 
WHERE [ReportPK]=@rptPK AND [RFTable]='Reports' AND [RFField]='SCField1'
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportFields Row - Reports.SCField1'
END
ELSE
BEGIN
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 7994, null, 'Reports', 'SCField1', null, 1, 1, 0, 'Smart Button Field Value', 0, 0, 1, 'Reports.SDPKField', null, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - Reports.SCField1'
IF (@@error > 0) 
BEGIN
SET @errorcount = @errorcount + 1
SET @tempPK = -1
END
ELSE
BEGIN
SET @tempPK = @@IDENTITY
END
END

IF @version >= 4.2 
BEGIN
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
IF EXISTS (
SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFTable')
BEGIN
SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFTable'
UPDATE ReportFields
SET [ReportPK]=@rptPK, [DataDictPK]=3788, [AGFunction]=null, [Alias]=null, [DisplayOrder]=2, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Table saving to', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=0, [CustomExpression]=null, [DemoLaborPK]=0, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]='  ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null 
WHERE [ReportPK]=@rptPK AND [RFTable]='ReportFields' AND [RFField]='RFTable'
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportFields Row - ReportFields.RFTable'
END
ELSE
BEGIN
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3788, null, 'ReportFields', 'RFTable', null, 2, 1, 0, 'Table saving to', 0, 0, 0, null, 0, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - ReportFields.RFTable'
IF (@@error > 0) 
BEGIN
SET @errorcount = @errorcount + 1
SET @tempPK = -1
END
ELSE
BEGIN
SET @tempPK = @@IDENTITY
END
END

IF @version >= 4.2 
BEGIN
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
IF EXISTS (
SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFField')
BEGIN
SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFField'
UPDATE ReportFields
SET [ReportPK]=@rptPK, [DataDictPK]=3789, [AGFunction]=null, [Alias]=null, [DisplayOrder]=3, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Field to be Saved', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=0, [CustomExpression]=null, [DemoLaborPK]=0, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]='  ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null 
WHERE [ReportPK]=@rptPK AND [RFTable]='ReportFields' AND [RFField]='RFField'
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportFields Row - ReportFields.RFField'
END
ELSE
BEGIN
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3789, null, 'ReportFields', 'RFField', null, 3, 1, 0, 'Field to be Saved', 0, 0, 0, null, 0, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - ReportFields.RFField'
IF (@@error > 0) 
BEGIN
SET @errorcount = @errorcount + 1
SET @tempPK = -1
END
ELSE
BEGIN
SET @tempPK = @@IDENTITY
END
END

IF @version >= 4.2 
BEGIN
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
IF EXISTS (
SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'UDFChar1')
BEGIN
SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'UDFChar1'
UPDATE ReportFields
SET [ReportPK]=@rptPK, [DataDictPK]=3725, [AGFunction]=null, [Alias]=null, [DisplayOrder]=4, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Save Table PK', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=1, [CustomExpression]='(SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
	AND TABLE_NAME = ReportFields.RFTable)', [DemoLaborPK]=0, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]='  ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null 
WHERE [ReportPK]=@rptPK AND [RFTable]='Reports' AND [RFField]='UDFChar1'
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportFields Row - Reports.UDFChar1'
END
ELSE
BEGIN
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3725, null, 'Reports', 'UDFChar1', null, 4, 1, 0, 'Save Table PK', 0, 0, 1, '(SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
	AND TABLE_NAME = ReportFields.RFTable)', 0, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - Reports.UDFChar1'
IF (@@error > 0) 
BEGIN
SET @errorcount = @errorcount + 1
SET @tempPK = -1
END
ELSE
BEGIN
SET @tempPK = @@IDENTITY
END
END

IF @version >= 4.2 
BEGIN
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
/* =====================================================
UPDATE REPORTS COLS IN DATA DICT - (RPTLISTSmartAudit)
===================================================== */
UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFField' 

PRINT 'Updating DataDict Row - ReportFields.RFField'
IF (@@error > 0) SET @errorcount = @errorcount + 1

UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFTable' 

PRINT 'Updating DataDict Row - ReportFields.RFTable'
IF (@@error > 0) SET @errorcount = @errorcount + 1

UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'Reports' AND COLUMN_NAME = 'ReportName' 

PRINT 'Updating DataDict Row - Reports.ReportName'
IF (@@error > 0) SET @errorcount = @errorcount + 1

UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'Reports' AND COLUMN_NAME = 'UDFChar1' 

PRINT 'Updating DataDict Row - Reports.UDFChar1'
IF (@@error > 0) SET @errorcount = @errorcount + 1

END
/* REPORT DID NOT EXIST, CREATE ALL RECORDS */
ELSE

BEGIN
PRINT '*******************************************************'
PRINT 'Report Does Not Exist - Inserting...'
PRINT 'Report: Report Smart Button Value w/ fields'
PRINT '*******************************************************'

/* =====================================================
INSERT REPORT RECORDS - (RPTLISTSmartAudit)
===================================================== */
/* INSERT Main Report */
INSERT INTO Reports ([ReportIDPriorToCopy], [ReportID], [ReportName], [ReportDesc], [RepairCenterPK], [Sort1], [Sort2], [Sort3], [Sort4], [Sort5], [Sort1DESC], [Sort2DESC], [Sort3DESC], [Sort4DESC], [Sort5DESC], [Group1], [Group2], [Group3], [Group4], [Group5], [Header1], [Header2], [Header3], [Header4], [Header5], [GroupHeader1], [GroupHeader2], [GroupHeader3], [GroupHeader4], [GroupHeader5], [Total1], [Total2], [Total3], [Total4], [Total5], [Chart], [ChartName], [ChartField], [ChartSize], [ReportFile], [FromSQL], [JoinSQL], [WhereSQL], [GroupBy], [hits], [Sequence], [Layout], [VertCols], [PageBreakEachRecord], [Custom], [ReportCopy], [MCRegistrationDB], [PrintCriteria], [Active], [UDFChar1], [UDFChar2], [UDFChar3], [UDFChar4], [UDFChar5], [UDFDate1], [UDFDate2], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionAction], [RowVersionDate], [ChartFunction], [ChartFunctionField], [NoDetail], [PB1], [PB2], [PB3], [PB4], [PB5], [SLDefault], [SLType], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip], [SDDisplay], [SDModuleID], [SDPKField], [SmartEmail], [ChartPosition], [ChartFormat], [ChartSQL], [Chart2], [ChartName2], [ChartField2], [ChartSize2], [ChartFormat2], [ChartFunction2], [ChartFunctionField2], [ChartPosition2], [ChartSQL2], [Chart3], [ChartName3], [ChartField3], [ChartSize3], [ChartFormat3], [ChartFunction3], [ChartFunctionField3], [ChartPosition3], [ChartSQL3], [ChartOnly], [NoHeader], [HavingSQL], [SRID1], [SRPKField1], [SRID2], [SRPKField2], [SRID3], [SRPKField3], [SRID4], [SRPKField4], [SRID5], [SRPKField5], [ReportPageSize], [ReportWidth], [PhotoCriteria], [ReportStyleName], [UsedFor], [SmartEmailLaborPK], [SCDefault], [SCField1], [SCField2], [SCField3], [ReportStyleFontSize], [ReportStyleFontColor], [ReportStyleFontFamily] )
VALUES('RPTLISTSmartAudit', 'RPTLISTSmartAudit', 'Report Smart Button Value w/ fields', 'This report will display the <b>Smart Button Field Value</b>. As a general rule, this field should be the <b>PK of the table being edited</b>.
For example, any change to a WO should display WO.WOPK in the Smart Button Field Value column.
<p>For more information clik here: <a href="https://uc.maintenanceconnection.com/hc/en-us/articles/203389184-Reporter-Tutorial-Advanced#editable" target="_blank" >Editable smart reports</a></p>
', null, 'ReportGroup.ReportGroupName', 'Reports.ReportName', 'Reports.ReportID', null, null, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, null, null, null, null, 0, 0, 0, 0, 0, null, null, 'SORT1', null, 'rpt_generic1.asp', 'FROM Reports', 'LEFT JOIN ReportFields with(nolock) ON ReportFields.ReportPK = Reports.ReportPK AND ReportFields.SLAction = ''EF''
LEFT JOIN Report_ReportGroup ON Report_ReportGroup.REportPK = Reports.ReportPK
LEFT JOIN ReportGroup ON ReportGroup.ReportGroupPK = Report_ReportGroup.ReportGroupPK', '(Reports.USEDFOR = ''REPORTS'' or Reports.USEDFOR Is NULL)
 AND Reports.SDDisplay != ''''
 AND Reports.SDDisplay IS NOT NULL
 AND (Reports.SDPKField != (SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
  AND TABLE_NAME = ReportFields.RFTable)
 OR ReportFields.RFTable IN (SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
  GROUP BY TABLE_NAME
  HAVING COUNT(COLUMN_NAME) > 1))', 0, 0, 0, 'hor', 1, 0, 0, 1, 0, 1, 1, null, null, null, 'N', null, null, null, null, '', '', '_MC', 'CREATE', getdate(), null, null, 0, 0, 0, 0, 0, 0, 0, ' ', 'PW', '  ', null, null, null, null, '     ', '  ', null, 0, null, 'I', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, 0, null, null, null, null, null, null, null, null, null, null, null, 'Default', '100%', 0, 'Default', 'REPORTS', 0, 'H', null, null, null, null, null, null)

PRINT 'Inserting Report - RPTLISTSmartAudit'
IF (@@error > 0) SET @errorcount = @errorcount + 1

SET @rptPK = @@Identity

IF @version >= 3.0 
BEGIN
SET @dSQL = 'UPDATE Reports SET [HavingSQL]=null, [DisplayPivotBar]=0, [DisplayColumnLines]=0, [DisplayTitleonPageBreak]=0, [DisplayFormatCriteria]=0, [R1T]='' '', [R1O]=''SQL            '', [R1V1]=null, [R1V2]=null, [R1A]=2, [R1L]=0, [R1F]=0, [R1CS]=''border: #0066CC 2px solid;'', [R1AF]=''C'', [R2T]='' '', [R2O]=null, [R2V1]=null, [R2V2]=null, [R2A]=0, [R2L]=0, [R2F]=0, [R2CS]=''border: #0066CC 2px solid;'', [R2AF]=''C'', [R3T]='' '', [R3O]=null, [R3V1]=null, [R3V2]=null, [R3A]=0, [R3L]=0, [R3F]=0, [R3CS]=''border: #0066CC 2px solid;'', [R3AF]=''C'' WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1

END

IF @version >= 4.2 
BEGIN
SET @dSQL = 'UPDATE Reports SET [DisplayDescription]=0 WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1

END

/* ==================================================
UPDATE OR INSERT REPORT GROUPS - (RPTLISTSmartAudit)
=================================================== */

/* Make sure the report group actually exists */
IF NOT EXISTS (SELECT ReportGroupPK FROM ReportGroup WHERE ReportGroupPK = 54)
BEGIN
INSERT INTO ReportGroup ([ReportGroupID], [ReportGroupName], [ModuleID], [Sequence], [Icon], [RepairCenterPK], [IsUserGroup], [IsBatchGroup], [UDFChar1], [UDFChar2], [UDFChar3], [UDFChar4], [UDFChar5], [UDFDate1], [UDFDate2], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionAction], [RowVersionDate]) 
VALUES ('SY', 'System', 'SY', 99, null, null, 0, 0, null, null, null, null, null, null, null, null, '', '', '_MC', 'CREATE', getdate())
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Inserting ReportGroup Row - ' + 'System'

SET @rptGroupPK = @@IDENTITY
END
ELSE
BEGIN
UPDATE	ReportGroup
SET	[ReportGroupID]='SY', [ReportGroupName]='System', [ModuleID]='SY', [Sequence]=99, [Icon]=null, [RepairCenterPK]=null, [IsUserGroup]=0, [IsBatchGroup]=0, [UDFChar1]=null, [UDFChar2]=null, [UDFChar3]=null, [UDFChar4]=null, [UDFChar5]=null, [UDFDate1]=null, [UDFDate2]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionAction]='EDIT', [RowVersionDate]=getdate()
WHERE	ReportGroupPK = 54
IF (@@error > 0) SET @errorcount = @errorcount + 1

PRINT 'Updating ReportGroup Row - ' + 'System'

SET @rptGroupPK = 54
END

INSERT INTO Report_ReportGroup ([ReportPK], [ReportGroupPK], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, @rptGroupPK, null, '', '', '_MC', getdate())
PRINT 'Inserting Report_ReportGroup Row - ' + 'System'
IF (@@error > 0) SET @errorcount = @errorcount + 1

/* ==================================================
INSERT REPORT TABLES- (RPTLISTSmartAudit)
=================================================== */	
INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportFields', null, 0, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportTables Row - ReportFields'
IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportGroup', null, 0, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportTables Row - ReportGroup'
IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', null, 0, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportTables Row - Reports'
IF (@@error > 0) SET @errorcount = @errorcount + 1


/* ==================================================
INSERT REPORT CRITERIA - (RPTLISTSmartAudit)
=================================================== */	
INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportID', 'Reports', 'ReportID', null, null, null, 0, 1, null, 0, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportCriteria Row - Reports.ReportID'
IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportName', 'Reports', 'ReportName', null, null, null, 0, 1, null, 1, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportCriteria Row - Reports.ReportName'
IF (@@error > 0) SET @errorcount = @errorcount + 1

INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'Active', 'Reports', 'Active', null, null, null, 0, 1, null, 2, null, null, '', '', '_MC', getdate())
PRINT 'Inserting ReportCriteria Row - Reports.Active'
IF (@@error > 0) SET @errorcount = @errorcount + 1

/* ==================================================
INSERT REPORT FIELDS - (RPTLISTSmartAudit)
=================================================== */	
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3716, null, 'Reports', 'ReportName', null, 0, 1, 0, null, 0, 0, 0, null, null, '', '', '_MC', getdate(), null, null, null, null, null, null)
PRINT 'Inserting ReportFields Row - Reports.ReportName'
IF (@@error > 0) SET @errorcount = @errorcount + 1

IF @version >= 4.2 
BEGIN
SET @tempPK = @@IDENTITY
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = null, [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 7994, null, 'Reports', 'SCField1', null, 1, 1, 0, 'Smart Button Field Value', 0, 0, 1, 'Reports.SDPKField', null, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - Reports.SCField1'
IF (@@error > 0) SET @errorcount = @errorcount + 1

IF @version >= 4.2 
BEGIN
SET @tempPK = @@IDENTITY
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3788, null, 'ReportFields', 'RFTable', null, 2, 1, 0, 'Table saving to', 0, 0, 0, null, 0, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - ReportFields.RFTable'
IF (@@error > 0) SET @errorcount = @errorcount + 1

IF @version >= 4.2 
BEGIN
SET @tempPK = @@IDENTITY
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3789, null, 'ReportFields', 'RFField', null, 3, 1, 0, 'Field to be Saved', 0, 0, 0, null, 0, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - ReportFields.RFField'
IF (@@error > 0) SET @errorcount = @errorcount + 1

IF @version >= 4.2 
BEGIN
SET @tempPK = @@IDENTITY
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip])
VALUES(@rptPK, 3725, null, 'Reports', 'UDFChar1', null, 4, 1, 0, 'Save Table PK', 0, 0, 1, '(SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
	AND TABLE_NAME = ReportFields.RFTable)', 0, '', '', '_MC', getdate(), '  ', null, null, null, null, null)
PRINT 'Inserting ReportFields Row - Reports.UDFChar1'
IF (@@error > 0) SET @errorcount = @errorcount + 1

IF @version >= 4.2 
BEGIN
SET @tempPK = @@IDENTITY
SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = ''  '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + '
'
EXEC(@dSQL)
IF (@@error > 0) SET @errorcount = @errorcount + 1
END
/* =====================================================
UPDATE REPORTS COLS IN DATA DICT - (RPTLISTSmartAudit)
===================================================== */

UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFField' 

PRINT 'Updating DataDict Row - ReportFields.RFField'
if (@@error > 0) Set @errorcount = @errorcount + 1

UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFTable' 

PRINT 'Updating DataDict Row - ReportFields.RFTable'
if (@@error > 0) Set @errorcount = @errorcount + 1

UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'Reports' AND COLUMN_NAME = 'ReportName' 

PRINT 'Updating DataDict Row - Reports.ReportName'
if (@@error > 0) Set @errorcount = @errorcount + 1

UPDATE DataDict 
SET	[REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 
WHERE	TABLE_NAME = 'Reports' AND COLUMN_NAME = 'UDFChar1' 

PRINT 'Updating DataDict Row - Reports.UDFChar1'
if (@@error > 0) Set @errorcount = @errorcount + 1

END

PRINT '*******************************************************'
IF (@errorcount > 0) PRINT @errorcount + ' Error(s) Occurred: RPTLISTSmartAudit'
ELSE PRINT 'No Errors Occurred: RPTLISTSmartAudit'